﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using InformationSystem.Entity;
using System.Data.SQLite;

namespace InformationSystem.Dao
{
    public class UserDao : BaseDao, IDao<User>
    {
        public bool add(User user)
        {
            String sql = "insert into t_user ( name, age, phone, address, userType, pwd) values"
                                             + "(@name,@age,@phone,@address,@userType,@pwd)";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",user.name.ToString()),
				helper.createSqliteParam("@age",user.age.ToString()),
				helper.createSqliteParam("@phone",user.phone.ToString()),
				helper.createSqliteParam("@address",user.address.ToString()),
				helper.createSqliteParam("@userType",user.userType.ToString()),				
                helper.createSqliteParam("@pwd",user.pwd.ToString())				
			};

            return helper.Exc(sql, param) == 1;
        }

        public bool del(User user)
        {
            String sql = "delete from t_user where id = @id";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@id",user.id.ToString())
			};

            return helper.Exc(sql, param) == 1;
        }

        public bool update(User user)
        {
            String sql = "update t_user  set name=@name, age=@age, phone=@phone, address=@address, userType=@userType, pwd=@pwd where id = @id";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",user.name.ToString()),
				helper.createSqliteParam("@age",user.age.ToString()),
				helper.createSqliteParam("@phone",user.phone.ToString()),
				helper.createSqliteParam("@address",user.address.ToString()),
				helper.createSqliteParam("@userType",user.userType.ToString()),				
                helper.createSqliteParam("@pwd",user.pwd.ToString()),
                helper.createSqliteParam("@id",user.id.ToString())
			};

            return helper.Exc(sql, param) == 1;
        }

        public User query(User user)
        {
            String sql = "select id,name, age, phone, address, userType, pwd from t_user where id = @id";
			SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@id",user.id.ToString())
			};
			
			SQLiteDataReader sdr = helper.Read(sql,param);
			
			User retUser = new User();
			
			while (sdr.Read()) 
			{
				retUser.id = sdr.GetInt32(0);
				retUser.name = sdr.GetString(1);
				retUser.age = sdr.GetInt32(2);
				retUser.phone = sdr.GetString(3);
				retUser.address = sdr.GetString(4);
				retUser.userType = sdr.GetInt32(5);
				retUser.pwd = sdr.GetString(6);
			}
			
			sdr.Close();
			sdr.Dispose();
			
			
			return retUser;
        }

        public List<User> queryAll()
        {
        	List<User> users = new List<User>();
        	
            String sql = "select id,name, age, phone, address, userType,pwd from t_user";
			
			SQLiteDataReader sdr = helper.Read(sql);
			
			while (sdr.Read()) 
			{
				User retUser = new User();
				retUser.id = sdr.GetInt32(0);
				retUser.name = sdr.GetString(1);
				retUser.age = sdr.GetInt32(2);
				retUser.phone = sdr.GetString(3);
				retUser.address = sdr.GetString(4);
				retUser.userType = sdr.GetInt32(5);
				retUser.pwd = sdr.GetString(6);
				users.Add(retUser);
			}
			
			sdr.Close();
			sdr.Dispose();
			
			return users;
        }

        public bool validUser(User user)
        {
            string sql = "select count(*) from t_user where name = @name and pwd = @pwd";
            bool isValidUser = false;
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",user.name),
				helper.createSqliteParam("@pwd",ISUtils.getInstance().encode(user.pwd))
			};

            SQLiteDataReader sdr = helper.Read(sql, param);

            while (sdr.Read())
            {
                int i = sdr.GetInt32(0);
                if (i > 0)
                {
                    isValidUser = true;
                }
            }

            return isValidUser;

        }
        
        public SQLiteDataAdapter getDisplayColumn(String sql)
		{        	
        	SQLiteDataAdapter da = helper.QueryDA(sql);
        	
        	return da;
		}



        public User queryByName(string name)
        {
            String sql = "select id,name, age, phone, address, userType, pwd from t_user where name = @name";
            SQLiteParameter[] param = 
			{
				helper.createSqliteParam("@name",name)
			};

            SQLiteDataReader sdr = helper.Read(sql, param);

            User retUser = new User();

            while (sdr.Read())
            {
                retUser.id = sdr.GetInt32(0);
                retUser.name = sdr.GetString(1);
                retUser.age = sdr.GetInt32(2);
                retUser.phone = sdr.GetString(3);
                retUser.address = sdr.GetString(4);
                retUser.userType = sdr.GetInt32(5);
                retUser.pwd = sdr.GetString(6);
            }

            sdr.Close();
            sdr.Dispose();


            return retUser;
        }
    }
}
